package dao.mysql;

import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.*;

import model.RoleDTO;
import dao.DAOException;
import dao.IRoleDAO;

public class RoleDAO implements IRoleDAO{
	Connection conn;
	
	public RoleDAO()
	{
		MyConnection myconn = new MyConnection();
		conn = myconn.getConnection();
	}

	@Override
	public RoleDTO findRole(String id) throws DAOException {
		String selectSql = "SELECT * FROM Role WHERE ID='"+id.trim()+"';";
		RoleDTO roleDTO = new RoleDTO();
		
		try
		{
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,"Executing Select:"+ selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while(rs.next())
			{
				roleDTO.setId(rs.getString("Id"));
				roleDTO.setName(rs.getString("Name"));
				roleDTO.setDescription(rs.getString("Description"));
				roleDTO.setCreatedBy(rs.getString("CreatedBy"));
				roleDTO.setCreatedOn(rs.getDate("CreatedOn"));
				roleDTO.setModifiedBy(rs.getString("ModifiedBy"));
				roleDTO.setModifiedOn(rs.getDate("ModifiedOn"));
			}
			st.close();
			
		}catch(SQLException sqlex)
		{
			String error = "Error Selecting role.Exception is: "+sqlex;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE,error);
			throw new DAOException();
		}
		finally
		{
			try
			{
				conn.close();
			}catch(Exception e){}
			
		}
		
		return roleDTO;
	}

	@Override
	public ArrayList<RoleDTO> findAllRole() throws DAOException {
		ArrayList<RoleDTO> items = new ArrayList<RoleDTO>();
		String selectSql = "SELECT * FROM Role";
		try
		{
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			
			while(rs.next())
			{
				RoleDTO roleDTO = new RoleDTO();
				roleDTO.setId(rs.getString("Id"));
				roleDTO.setName(rs.getString("Name"));
				roleDTO.setDescription(rs.getString("Description"));
				roleDTO.setCreatedBy(rs.getString("CreatedBy"));
				roleDTO.setCreatedOn(rs.getDate("CreatedOn"));
				roleDTO.setModifiedBy(rs.getString("ModifiedBy"));
				roleDTO.setModifiedOn(rs.getDate("ModifiedOn"));
			}
			st.close();
		}catch(SQLException sqlex)
		{
			String error = "Error Selecting role.Exception is: "+sqlex;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE,error);
			throw new DAOException();
		}
		finally
		{
			try
			{
				conn.close();
			}
			catch(Exception ex){};
			
		}
		
		return null;
	}

	@Override
	public void insertRole(RoleDTO role) throws DAOException {
		String insertSql = "INSERT INTO Role(Id,Name,Description,CreatedBy,CreatedOn,ModifiedBy,ModifiedOn)" +
				" VALUES(?,?,?,CURRENT_USER(),NOW(),CURRENT_USER(),NOW());";
		try
		{
			PreparedStatement pst = conn.prepareStatement(insertSql);
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing insert: " + insertSql);
			
			pst.setString(1, role.getId());
			pst.setString(2, role.getName());
			pst.setString(3, role.getDescription());
						
			if(pst.executeUpdate() != 1)
			{
				String error = "Error Inserting into role.";
				Logger.getLogger(getClass().getName()).log(Level.SEVERE,error);
				throw new DAOException();
			}
			
			pst.close();
		}catch(SQLException sqlex)
		{
			String error = "Error Inserting into role.Exception is: "+sqlex;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE,error);
			throw new DAOException();
		}
		finally
		{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
	}

	@Override
	public void updateRole(RoleDTO role) throws DAOException {
		String updateSql = "UPDATE Role SET Name=?,Description=?,ModifiedBy=CURRENT_USER(),ModifiedOn=NOW() WHERE Id=?";
		try
		{
			PreparedStatement pst = conn.prepareStatement(updateSql);
			pst.setString(1, role.getName());
			pst.setString(2, role.getDescription());
			pst.setString(3, role.getId());
			if(pst.executeUpdate() != 1)
			{
				String error = "Error updating Role.";
				Logger.getLogger(getClass().getName()).log(Level.SEVERE,error);
				throw new DAOException();
			}
			
			pst.close();
		}catch(SQLException sqlex)
		{
			String error = "Error Updating Role.Exception is: "+sqlex;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE,error);
			throw new DAOException();
		}
		finally
		{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}

	@Override
	public void deleteRole(RoleDTO role) throws DAOException {
		String updateSql = "DELETE FROM Role WHERE Id=?";
		try
		{
			PreparedStatement pst = conn.prepareStatement(updateSql);
			pst.setString(1, role.getId());
			
			if(pst.executeUpdate() != 1)
			{
				String error = "Error Deleting Role.";
				Logger.getLogger(getClass().getName()).log(Level.SEVERE,error);
				throw new DAOException();
			}
			
			pst.close();
		}catch(SQLException sqlex)
		{
			String error = "Error Deleting Role.Exception is: "+sqlex;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE,error);
			throw new DAOException();
		}
		finally
		{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}

}
